package io.aicn.lowcode.service;

import com.fasterxml.jackson.databind.ObjectMapper;
import io.aicn.lowcode.generator.model.ColumnInfo;
import io.aicn.lowcode.generator.model.TableInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class DatabaseSchemaService {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private ObjectMapper objectMapper;
    
    @Autowired
    private TableMetadataService tableMetadataService;

    public Map<String, Object> getDatabaseSchema() {
        Map<String, Object> schema = new HashMap<>();
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // Database information
            Map<String, Object> dbInfo = new HashMap<>();
            dbInfo.put("databaseName", connection.getSchema());
            dbInfo.put("databaseVersion", metaData.getDatabaseProductVersion());
            dbInfo.put("charset", "utf8mb4");
            dbInfo.put("collation", "utf8mb4_unicode_ci");
            schema.put("databaseInfo", dbInfo);
            
            // Tables
            List<Map<String, Object>> tables = new ArrayList<>();
            
            try (ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"})) {
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    Map<String, Object> tableDef = getTableDefinition(metaData, tableName);
                    tables.add(tableDef);
                }
            }
            
            schema.put("tables", tables);
            
        } catch (Exception e) {
            throw new RuntimeException("获取数据库模式失败: " + e.getMessage());
        }
        
        return schema;
    }

    private Map<String, Object> getTableDefinition(DatabaseMetaData metaData, String tableName) throws SQLException {
        Map<String, Object> tableDef = new HashMap<>();
        tableDef.put("tableName", tableName);
        tableDef.put("engine", "InnoDB");
        tableDef.put("charset", "utf8mb4");
        tableDef.put("collation", "utf8mb4_unicode_ci");
        
        // Table comment
        try (ResultSet rs = metaData.getTables(null, null, tableName, new String[]{"TABLE"})) {
            if (rs.next()) {
                tableDef.put("tableComment", rs.getString("REMARKS"));
            }
        }
        
        // Columns
        List<Map<String, Object>> columns = new ArrayList<>();
        try (ResultSet rs = metaData.getColumns(null, null, tableName, null)) {
            while (rs.next()) {
                Map<String, Object> column = getColumnDefinition(rs);
                columns.add(column);
            }
        }
        tableDef.put("columns", columns);
        
        // Primary key
        Map<String, Object> primaryKey = getPrimaryKeyDefinition(metaData, tableName);
        if (primaryKey != null) {
            tableDef.put("primaryKey", primaryKey);
        }
        
        // Indexes
        List<Map<String, Object>> indexes = getIndexDefinitions(metaData, tableName);
        if (!indexes.isEmpty()) {
            tableDef.put("indexes", indexes);
        }
        
        // Foreign keys
        List<Map<String, Object>> foreignKeys = getForeignKeyDefinitions(metaData, tableName);
        if (!foreignKeys.isEmpty()) {
            tableDef.put("foreignKeys", foreignKeys);
        }
        
        return tableDef;
    }

    private Map<String, Object> getColumnDefinition(ResultSet rs) throws SQLException {
        Map<String, Object> column = new HashMap<>();
        column.put("name", rs.getString("COLUMN_NAME"));
        column.put("type", rs.getString("TYPE_NAME"));
        column.put("length", rs.getInt("COLUMN_SIZE"));
        column.put("nullable", rs.getInt("NULLABLE") == 1);
        column.put("defaultValue", rs.getString("COLUMN_DEF"));
        column.put("comment", rs.getString("REMARKS"));
        
        // Handle decimal precision
        int decimalDigits = rs.getInt("DECIMAL_DIGITS");
        if (decimalDigits > 0) {
            column.put("decimals", decimalDigits);
        }
        
        return column;
    }

    private Map<String, Object> getPrimaryKeyDefinition(DatabaseMetaData metaData, String tableName) throws SQLException {
        try (ResultSet rs = metaData.getPrimaryKeys(null, null, tableName)) {
            List<String> pkColumns = new ArrayList<>();
            String pkName = null;
            
            while (rs.next()) {
                pkName = rs.getString("PK_NAME");
                pkColumns.add(rs.getString("COLUMN_NAME"));
            }
            
            if (!pkColumns.isEmpty()) {
                Map<String, Object> primaryKey = new HashMap<>();
                primaryKey.put("name", pkName);
                primaryKey.put("columns", pkColumns);
                return primaryKey;
            }
        }
        return null;
    }

    private List<Map<String, Object>> getIndexDefinitions(DatabaseMetaData metaData, String tableName) throws SQLException {
        List<Map<String, Object>> indexes = new ArrayList<>();
        
        try (ResultSet rs = metaData.getIndexInfo(null, null, tableName, false, false)) {
            Map<String, Map<String, Object>> indexMap = new HashMap<>();
            
            while (rs.next()) {
                String indexName = rs.getString("INDEX_NAME");
                if (indexName == null || "PRIMARY".equals(indexName)) {
                    continue; // Skip primary key
                }
                
                Map<String, Object> index;
                try {
                    index = indexMap.computeIfAbsent(indexName, k -> {
                        try {
                            Map<String, Object> idx = new HashMap<>();
                            idx.put("name", indexName);
                            idx.put("type", rs.getBoolean("NON_UNIQUE") ? "INDEX" : "UNIQUE");
                            idx.put("columns", new ArrayList<>());
                            return idx;
                        } catch (SQLException e) {
                            throw new RuntimeException(e);
                        }
                    });
                } catch (RuntimeException e) {
                    if (e.getCause() instanceof SQLException) {
                        throw (SQLException) e.getCause();
                    }
                    throw e;
                }
                
                @SuppressWarnings("unchecked")
                List<String> columns = (List<String>) index.get("columns");
                columns.add(rs.getString("COLUMN_NAME"));
            }
            
            indexes.addAll(indexMap.values());
        }
        
        return indexes;
    }

    private List<Map<String, Object>> getForeignKeyDefinitions(DatabaseMetaData metaData, String tableName) throws SQLException {
        List<Map<String, Object>> foreignKeys = new ArrayList<>();
        
        try (ResultSet rs = metaData.getImportedKeys(null, null, tableName)) {
            while (rs.next()) {
                Map<String, Object> fk = new HashMap<>();
                fk.put("name", rs.getString("FK_NAME"));
                fk.put("column", rs.getString("FKCOLUMN_NAME"));
                fk.put("referenceTable", rs.getString("PKTABLE_NAME"));
                fk.put("referenceColumn", rs.getString("PKCOLUMN_NAME"));
                fk.put("onDelete", rs.getString("DELETE_RULE"));
                fk.put("onUpdate", rs.getString("UPDATE_RULE"));
                foreignKeys.add(fk);
            }
        }
        
        return foreignKeys;
    }

    public String getDatabaseSchemaAsJson() {
        try {
            Map<String, Object> schema = getDatabaseSchema();
            return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(schema);
        } catch (Exception e) {
            throw new RuntimeException("生成JSON模式失败: " + e.getMessage());
        }
    }

    public TableInfo getTableInfo(String tableName) throws SQLException {
        // 首先尝试使用新的表元数据服务解析
        try {
            return tableMetadataService.parseTableDefinitionByName(tableName);
        } catch (Exception e) {
            // 如果新格式解析失败，回退到传统的数据库元数据解析
            return getTableInfoFromDatabase(tableName);
        }
    }
    
    /**
     * 从数据库元数据获取表信息（传统方式）
     * @param tableName 表名
     * @return TableInfo对象
     * @throws SQLException SQL异常
     */
    public TableInfo getTableInfoFromDatabase(String tableName) throws SQLException {
        TableInfo tableInfo = new TableInfo();
        tableInfo.setTableName(tableName);
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 获取表信息
            try (ResultSet rs = metaData.getTables(null, null, tableName, new String[]{"TABLE"})) {
                if (rs.next()) {
                    tableInfo.setTableComment(rs.getString("REMARKS"));
                }
            }
            
            // 获取列信息
            List<ColumnInfo> columns = new ArrayList<>();
            try (ResultSet rs = metaData.getColumns(null, null, tableName, null)) {
                while (rs.next()) {
                    ColumnInfo column = new ColumnInfo();
                    column.setName(rs.getString("COLUMN_NAME"));
                    column.setType(rs.getString("TYPE_NAME"));
                    column.setLength(rs.getInt("COLUMN_SIZE"));
                    column.setNullable(rs.getInt("NULLABLE") == 1);
                    column.setDefaultValue(rs.getString("COLUMN_DEF"));
                    column.setComment(rs.getString("REMARKS"));
                    column.setAutoIncrement("YES".equals(rs.getString("IS_AUTOINCREMENT")));
                    
                    // 设置Java类型
                    column.setJavaType(getJavaType(column.getType()));
                    column.setJavaProperty(underscoreToCamel(column.getName()));
                    
                    columns.add(column);
                }
            }
            
            // 获取主键信息
            try (ResultSet rs = metaData.getPrimaryKeys(null, null, tableName)) {
                while (rs.next()) {
                    String pkColumnName = rs.getString("COLUMN_NAME");
                    columns.stream()
                            .filter(col -> col.getName().equals(pkColumnName))
                            .forEach(col -> col.setPrimaryKey(true));
                }
            }
            
            tableInfo.setColumns(columns);
        }
        
        return tableInfo;
    }

    private String getJavaType(String sqlType) {
        if (sqlType == null) return "Object";
        
        sqlType = sqlType.toUpperCase();
        
        if (sqlType.contains("INT") || sqlType.contains("INTEGER")) {
            return "Integer";
        } else if (sqlType.contains("BIGINT")) {
            return "Long";
        } else if (sqlType.contains("FLOAT") || sqlType.contains("REAL") || sqlType.contains("DOUBLE")) {
            return "Double";
        } else if (sqlType.contains("DECIMAL") || sqlType.contains("NUMERIC")) {
            return "BigDecimal";
        } else if (sqlType.contains("DATE") || sqlType.contains("TIME") || sqlType.contains("YEAR")) {
            return "Date";
        } else if (sqlType.contains("TIMESTAMP") || sqlType.contains("DATETIME")) {
            return "LocalDateTime";
        } else if (sqlType.contains("BLOB") || sqlType.contains("BINARY")) {
            return "byte[]";
        } else if (sqlType.contains("CLOB") || sqlType.contains("TEXT")) {
            return "String";
        } else if (sqlType.contains("BOOLEAN") || sqlType.contains("BIT")) {
            return "Boolean";
        } else {
            return "String";
        }
    }
    
    private String underscoreToCamel(String input) {
        if (input == null || input.isEmpty()) {
            return input;
        }
        
        StringBuilder result = new StringBuilder();
        boolean nextUpper = false;
        
        for (int i = 0; i < input.length(); i++) {
            char currentChar = input.charAt(i);
            if (currentChar == '_') {
                nextUpper = true;
            } else {
                if (nextUpper) {
                    result.append(Character.toUpperCase(currentChar));
                    nextUpper = false;
                } else {
                    result.append(Character.toLowerCase(currentChar));
                }
            }
        }
        
        return result.toString();
    }
}